﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.SQLite;
using System.Configuration;

namespace testSqlite
{
    public partial class Form1 : Form
    {
        private SQLiteConnection cnn = null;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
            cnn = (SQLiteConnection)(fact.CreateConnection());
            cnn.ConnectionString = "Data Source=..\\..\\data\\northwindEF.db";
            cnn.Open();
            
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (null != cnn && cnn.State != ConnectionState.Closed )
            {
                cnn.Close();
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {


            try
            {
                using (SQLiteTransaction mytransaction = cnn.BeginTransaction())
                {
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        SQLiteParameter myparam = new SQLiteParameter();
                        int n;

                        mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)";
                        mycommand.Parameters.Add(myparam);

                        for (n = 0; n < 100000; n++)
                        {
                            myparam.Value = n + 1;
                            mycommand.ExecuteNonQuery();
                        }
                    }
                    mytransaction.Commit();
                }
                MessageBox.Show("插入数据成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入数据失败");
                throw;
            }

        }

        private void button4_Click(object sender, EventArgs e)
        {
            using (SQLiteCommand cmd = new SQLiteCommand(cnn))
            {
                cmd.CommandText = "SELECT * FROM [MyTable]";
                SQLiteDataReader dr = cmd.ExecuteReader();
                dr.Read();
                dataGridView1.DataSource = dr;
                
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteTransaction mytransaction = cnn.BeginTransaction())
                {
                    using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                    {
                        SQLiteParameter myparam = new SQLiteParameter();
                        int n;

                        mycommand.CommandText = "INSERT INTO [testchina] ([firstname]) VALUES(?)";
                        mycommand.Parameters.Add(myparam);

                        
                            myparam.Value = "这是一个中文测试";
                            mycommand.ExecuteNonQuery();
                       
                    }
                    mytransaction.Commit();
                }
                MessageBox.Show("插入数据成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入数据失败");
                throw;
            }
        }

        private void button6_Click(object sender, EventArgs e)
        {
            SQLiteDataAdapter sqliteDaNew = new SQLiteDataAdapter("select * from MyTable", cnn);    
            SQLiteCommandBuilder sqliteCbNew = new SQLiteCommandBuilder(sqliteDaNew);    
                             
            DataSet sqliteDsNew = new DataSet();
            sqliteDaNew.Fill(sqliteDsNew);
            dataGridView1.DataSource = sqliteDsNew.Tables[0];
            
            
        }

        private void button7_Click(object sender, EventArgs e)
        {
            try
            {
                using (SQLiteCommand mycommand = new SQLiteCommand(cnn))
                {
                    SQLiteParameter myparam = new SQLiteParameter(); 

                    mycommand.CommandText = "DELETE from [MyTable]";
                    mycommand.ExecuteNonQuery();
                }
                using (SQLiteTransaction mytransaction = cnn.BeginTransaction())
                {
                    DBScript.SQLiteHelper sqlHelper = new DBScript.SQLiteHelper();
                    int n;
                    for (n = 0; n < 1000; n++)
                    {
                        SQLiteParameter param = new SQLiteParameter();
                        param.Value = n;
                        sqlHelper.ExecuteNonQuery(mytransaction, "INSERT INTO [MyTable] ([MyId]) VALUES(?)", param);
                    }
                    mytransaction.Commit();
                }
                MessageBox.Show("插入数据成功");
            }
            catch (Exception ex)
            {
                MessageBox.Show("插入数据失败");
                throw;
            }
        }
    }
}
